package de.fuberlin.inf.dbs.ue07;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

import de.fuberlin.inf.utils.JSON;

/**
 * @author stefanr
 *
 */
public class DbCon {
	private static DbCon instance;

	private Connection con;
	private PreparedStatement neighbours;
	private PreparedStatement bigCities;
	private final String NEIGHBOURS =
		"SELECT DISTINCT n.name FROM mondial.country n, mondial.borders b, mondial.country c " +
		"WHERE " +
		"  c.name LIKE ? AND (" +
		"    (c.code = b.country1 AND b.country2 = n.code) OR " +
		"    (c.code = b.country2 AND b.country1 = n.code) " +
		"  )" +
		"ORDER BY n.name";
	private final String BIG_CITIES =
		"SELECT * FROM TABLE (BIG_CITIES(?, ?))";
	
	private DbCon() {
		this.con = BaseDAO.getConnection("dbs_12", "dbs_12");
	}
	
	public synchronized static DbCon getInstance() {
		if (instance == null)
			instance = new DbCon();
		return instance; 
	}
	
	public void printAllNeighbors(String country) {
		try {
			if (this.neighbours == null)
				this.neighbours = this.con.prepareStatement(this.NEIGHBOURS);
			this.neighbours.setString(1, country);
			ResultSet rs = this.neighbours.executeQuery();
			LinkedList<String> neighbors = new LinkedList<String>();
			while(rs.next())
				neighbors.add(rs.getString("name"));
			String json = JSON.unsafe(neighbors);
			System.out.println(json);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public List<City> listBigCities(String country, int threshold) {
		LinkedList<City> cities = new LinkedList<City>();
		try {
			if (this.bigCities == null)
				this.bigCities = this.con.prepareStatement(this.BIG_CITIES);
			this.bigCities.setString(1, country);
			this.bigCities.setInt(2, threshold);
			ResultSet rs = this.bigCities.executeQuery();
			while(rs.next())
				cities.add(new City(rs.getString("name"), rs.getString("province"), rs.getInt("population")));
			return cities;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}
	
	public void close() {
		BaseDAO.closeConnection(this.con);
		this.con = null;
		DbCon.instance = null;
	}
}
